After installing the Excel Add-In, there will be a OneStream menu item and a ribbon in Excel. The ribbon contains these categories:
NOTE: When working in Excel, decimals are automatically truncated after the ninth character in a cell or a function.
OneStream
This displays the current user and application. A user can log off or logon to a different application by clicking this icon.
Explore
The Explore category enables you to quickly build a Quick View, Cube View, or Table View in Spreadsheet or Excel.
Quick Views
Create Quick View
This will create a new Quick View in the worksheet’s selected cell.
Copy Selected Quick View
This will copy the selected Quick View in order to paste a version of it in another spreadsheet or workbook.
Paste Quick View
This will paste the copied quick view to the current spreadsheet or another workbook.
Create Quick View Using POV from Selected Cell
This will create a new Quick View based on the current POV from the selected cell. This can be done using a Quick View cell’s POV or a Cube View cell’s POV.
For more details on this feature, see Quick View in About Excel Add-In.
Cube Views
Add a Cube View to an Excel sheet.
-
Click Cube Views > Cube View Connections.
-
From this window, the cube views added to an Excel workbook can be managed. You can add, remove, edit, or go to styles. Click Add, to add a new Cube View.
-
Name the connection and then choose the Cube View.
-
Populate the following fields:
-
Resize Initial Column Widths Using Cube View Settings is the default setting. If you disable it, you can change the columns and save the Cube View. However, if you go back into the same Cube View Connection, the check box will be enabled and you’ll need to disable it again to keep your new cube view settings.
-
Insert or Delete Rows/Colums When Resizing Cube View Content: Select whether there needs to be inserted or deleted rows and/or columns when resizing. This setting will move around other content in the sheets if the size of the Cube View changed since the last refresh.
-
Include Cube View Header: Choose whether to add header rows to the spreadsheet.
-
Retain Formulas in Cube View Content allows you to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed the formulas will remain. If the value resulting from the value is different than the value of the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow. See Retain Formulas in Cube View Content in Excel and Spreadsheet.
NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless Dynamically Highlighted Evaluated Cells is turned on.
-
Preserve Excel Formatting: This option enables you to preserve native Excel formatting changes made to cube views, quick views, and table views. When enabled, formatting changes made via native Excel formatting will be retained. See Preserve Formatting.
-
-
Click the OK button and then the Close button to view your Cube View.
After the Cube View is added, it will appear on the sheet. If formatting was applied to the Cube View, see Cube Views in Presentation, the formatting will come forward into the Excel sheet. Otherwise, apply Excel Styles. These styles are stored in the Excel sheet and can be copied between workbooks. For more information on Excel Styles, see Styles. If you enabled Preserve Excel Format, your formatting will remain. See Preserve Formatting.
NOTE: In order to copy Excel spreadsheet cells into a Data Explorer Grid on the web, click CTRL, select the cells desired, and then click CTRL-C. Navigate to the Data Explorer Grid, select a cell, and click CTRL-V, this will paste the cells into the grid. This can also be done from a Data Explorer Grid into an Excel Spreadsheet.
Retain Formulas in Cube View Content in Excel and Spreadsheet
Retain Formulas in Cube View Content allows you to form Cube View grids of data in Excel, using the Cube Views menu function, that can be linked to other Excel models for easy submission into OneStream. This feature allows formulas, (to writeable cells,) in Excel (or Spreadsheet) for an attached Cube View to be retained on submission and retrieval instead of being replaced with the value of the represented formula.
The Retain Formulas in Cube View Content feature, allows users to plan, budget or forecast and use the familiar functionality of Excel while still submitting data back to the OneStream database.
Use the Retain Formulas in Cube View Content feature to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed, the formulas will remain. If the value resulting from the formula differs from the existing value in the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow.
NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless you’ve turned on Dynamically Highlight Evaluated Cells in the cube view.
Retain Formulas in Cube View Content links to other Excel worksheets or worksheets in other Excel workbooks.
-
From the OneStream menu, select Cube Views > Cube View Connections.
-
Click Add in the Cube View Connection window or click Edit if you already have a cube view.
-
Click Retain Formulas in Cube View Content box and click the OK button.
-
Add the Cube View, if one is not already selected, and click the Close button.
Dynamically Highlight Evaluated Cells in Excel or Spreadsheet
When Retain Formulas in Cube View Content is enabled, the option to Dynamically Highlight Evaluated Cells becomes available to enable. When it’s enabled, every time you make a change to a cell in Excel or Spreadsheet that is referenced in a Cube View, the cell will immediately update and show the update with a change in color. This cell update is called a dirty cell, which indicates that the cell value is different from the information in the OneStream database.
Dynamically Highlight Evaluated Cells saves you a step because the cell changes without requiring a refresh. This feature evaluates all the cells in the spreadsheet and identifies the values in the cube view that have changed relative to its original value in the database.
Excel users who want to continue working in Excel to access can log in through the OneStream menu, update the cube view content and submit it to the database without leaving Excel. You can also perform these tasks in Spreadsheet within the application.
You can Retain Formulas in a Cube View Content that are related to values within a function, existing workbook, sheet, other sheets, in external workbooks, and in external renamed worksheets in Excel. Spreadsheet also offers this functionality, but it doesn’t allow you to point the cell references to external workbooks.
Click Refresh Sheet to see all changes within the cube view content and then click Submit Sheet or activate Dynamically Highlight Evaluated Cells and the cell updates automatically.
The number of cells with formulas in the cube view determines the amount of time it takes to update the cells. You can turn the feature on or off and only use Refresh Sheet to update the values in the cells. Changes will show very quickly, no matter the size of the worksheet, when using Spreadsheet.
Using Retain Formulas and Dynamically Highlight Evaluated Cells
You can also use retain formulas and dynamically highlighted evaluated cells within a cube view to automatically display updated values in an existing workbook, a sheet or sheets, external workbooks, and external renamed worksheets in Excel. You can also do this in Spreadsheet within the OneStream application, however, you can’t point the cell references to external workbooks.
-
In Excel, go to the OneStream menu and Log on.
-
Click Cube Views > Cube View Connections.
-
Click the Add button.
-
In the Cube View Connection window, click the ellipsis next to the Cube View field.
-
Select your choice and click the OK button.
-
Click Retain Formulas in Cube View Content to activate Dynamically Highlight Evaluated Cells.
-
Then click Dynamically Highlight Evaluated Cells so you can see the changes as they are made.
-
Even if you don’t activate the dynamically highlight evaluated cells feature, you can click Refresh Sheet after you make changes to see them.
-
If you’re prompted, click OK once you’ve selected the parameters for the cube view.
-
Once the cube view has been added, you can click Edit to review, if needed.
-
Make changes to the sheet and press <Enter> to see the updated cell, which will change from white to yellow.
-
Click Submit Sheet to automatically save changes to the database.
Use Cases
These use cases are for both Excel and Spreadsheet unless otherwise noted.
The placing of formulas or cell references. Retain Formulas can reference the following types of formulas. In all instances the formula will stay after refresh and/or submission.
Cell References of individual cells of data on the same sheet.
Cell References to a cell on the same sheet, factored by another value.
Cell References to cells on other sheets. These can also be factored by another value as well.
Referenced cell(s) on another saved workbook can also be factored by another value.
NOTE: This applies to Excel only.
Best Practices
Well-Formed Grid
It is suggested to create a “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views. When using this “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views, the Excel/SpreadSheet relative (=C2) and absolute formulas (=$C$2) will be retained.
However, when using these relative and absolute formulas within an Excel and Spreadsheet formula, users can use either the cell reference or text within the formula depending upon how members will be added or removed:
-
=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will work in a List or Comma-Separated list (Well-formed grid) when Accounts are added to the end.
-
=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a case when a Member of a Row is moved up or down.
Member Expansion Functions
When using Member Expansion Functions in Cube Views for Excel and SpreadSheet, the cell being referenced within the function (Vlookup, etc), will need to be adjusted and/or referenced as text.
-
=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a Dynamic or when a Member of a Row is moved.
-
=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will NOT work in a Dynamic or when a Member of a Row is moved as this is using the cell ref of D30.
Other Considerations
-
Deselecting the Retain Formulas for Cube View Content will eliminate all formulas that were established /existed on the Cube View grid.
-
Pivoting the existing Dimensions of the Cube View will break formulas.
-
Changing the “structure” of the Cube View grid in the rows or columns will also break the formulas. For example; If you have Account, Entity, UD3 as the dimensions used in the row and switch it to UD3, Entity, Account, it will break the formulas.
-
Users can change the POV to select a new dimension. This will change the Cube View results but retain the existing formulas that were established. The user at this point can choose to utilize the existing formulas, modify or delete. If the original formulas are modified or deleted, the last action will be saved.
-
Linking a white cell (writeable cell) to another cell in a different workbook will work ONLY in Excel and NOT in Spreadsheet.
-
Prior to establishing links to an external workbook, the user should save the external workbook being referenced.
-
When the user renames or saves as the (referenced) file, the user will need to update the links to the newly created file. Updating the links on the spreadsheet should be done BEFORE doing a refresh or submit.
-
Formulas with cell references (VLOOKUP, INDEX(MATCH(, etc) that return errors (#N/A, #ERROR, etc) or non-numeric data will not retain the formula and return to its original value from the Cube View ; this error text cannot be converted into a number so the formulas will not retain.
-
If a Dimension Member Name is renamed; i.e.; “52200 – Rent” is now “52200 – Rent Commercial”, the formula will break.
Table Views
Insert a Table View into Excel.
-
Click Table Views to open the Table View Definition dialog box.
-
From this window, manage the Table Views within Excel. Add, remove, edit, and refresh tables. Click the Add button to add a new Table View.
-
In the Table View Definition dialog box, populate the following fields:
-
Name: Table View name.
-
Refers To: Cell range where the Table View will exist.
-
Table View Business Rule: Click the ellipses to search for and select a business rule.
-
Insert or Delete Rows When Resizing Table View Content: Select this option if you plan to stack everything vertically on your worksheet. This automatically adds or deletes rows as a table view expands and contracts.
-
Insert or Delete Columns When Resizing Table View Content: Select this option if you plan to stack everything horizontally on your worksheet. This automatically adds or deletes columns as a cube view expands and contracts.
-
Preserve Excel Format: This option enables you to preserve native Excel formatting changes made to cube views, quick views, and table views. When enabled, formatting changes made via native Excel formatting will be retained. See Preserve Formatting.
-
-
Click the OK button, then the Close button to view the Table View in the Excel worksheet.
Analysis
Data Attachments
This pulls up the Data Attachments dialog to show existing comments or attachments on a selected cell, or to allow data attachment edits.
Cell Detail
Enter Cell Detail for a Cube View or Quick View data cell. See Cell Detail in Using OnePlace Cube Views for more details on this function.
Drill Down
Drill down on a specific cell in order to see more details or gather more information. See Drill Down in Using OnePlace Cube Views for more details on this function.
Copy POV from Data Cell
This captures the Point Of View of the currently selected cell. After clicking this option, the Paste POV As XFGetCell becomes available and the Copy POV From Data Cell goes to gray. The ability to paste this into another cell is now available and OneStream will automatically convert this into an XFGetCell formula with all of the appropriate Parameters.
Paste POV As XFGetCell
This option is only available after clicking Copy POV From Data Cell. After clicking this option, OneStream will convert the copied cell into an XFGetCell formula. Click Refresh Data to retrieve the data.
Convert to XFGetCells
This will convert an existing Quick View into an XFGetCells. After clicking this option, OneStream will prompt with the following: Are you sure you want to convert all of the data in Quick View ‘Name of the Quick View’ to XFGetCells? By clicking OK, the Quick View definition will be deleted and converted to XFGetCells.
Refresh
Refresh Workbook
This pulls down updated data from the server and refreshes the entire Excel workbook.
Refresh Worksheet
This pulls down updated data from the server and only refreshes the selected worksheet.
| Refresh Sheet | Refresh Workbook | |
|---|---|---|
| Function Behavior | Refreshes the selected tab only | Refreshes all tabs in the file |
| Data Impacts | Clears all dirty cells on the selected tab only | Clears all dirty cells on all tabs regardless of selected tab |
| Parameter Impacts (CV Only) | Prompts the user with any Parameters used on the selected tab | Prompts the user with all the Parameters used in the workbook |
Calculation
Consolidate/Translate/Calculate
If permission is granted, these calculations can be performed on the selected cell.
Submit
Submit Workbook
After editing data in Excel, click this icon to send it back to OneStream. This icon will send data back for every tab in the Excel workbook.
Submit Sheet
After editing data in Excel, click this icon to send it back to OneStream for Cube Views, Quick Views, XFSetCells, and Table Views on the active sheet.
| Submit Sheet | Submit Workbook | |
|---|---|---|
| Function Behavior | Identifies data changes on the selected table and stores these changes to the database | Identifies data changes on every tab and stores these changes to the database |
| Data Impacts | Submits all data for the selected tab only | Submits all data for all tabs |
| Parameter Impacts (CV Only) | No prompts | No prompts |
Spreading
Enables users to see what type of spreading was used to spread data values over several columns or rows without having to type in each cell’s values.
Spreading Types
Fill
This fills each selected data cell with the value in the Amount to Spread property.
Clear Data
This clears all data within the selected cells.
Factor
Multiply all cells by the specified rate.
Accumulate
This takes the first selected cell’s value and multiplies it by the rate specified. It then takes that value, multiplies it by the specified rate and places it in the second cell selected, and does this for all selected cells. For example, four cells are selected and the first cell has a value of 900.
Even Distribution
This takes the Amount to Spread and distributes it evenly across the selected cells.
445 Distribution
This takes the Amount to Spread and distributes it with a weight of 4 to the first two selected cells and a weight of 5 to the third cell.
454 Distribution
This takes the Amount to Spread and distributes it with a weight of 4 to the first selected cell, a weight of 5 to the second cell and a weight of 4 to the third.
544 Distribution
This takes the Amount to Spread and distributes it with a weight of 5 to the first selected cell and a weight of 4 to the second and third cells.
The Accumulate Spreading is setup as follows with a rate of 1.5:
When the spreading is applied the outcome is as follows:
Each cell’s value is a factor of the previous cell amount.
Proportional Distribution
This takes the selected cell’s value, multiplies it by the specified Amount to Spread, and then divides it by the total sum of all selected cells. If all the cells have a zero value, the Amount to Spread will behave like an Even Distribution.
A proportional amount of 50,000 is applied to the cells:
Result:
Spreading Properties
Amount to Spread
Specify the value to spread over the selected cells. The value defaults to the last cell selected. The way the amount in this field spreads varies by Spreading Type.
Rate (Factor and Accumulate Spreading Types Only) Enter a rate to multiply by a cell value.
Retain Amount in Flagged Input Cells
Users can flag specific cells in order to retain the data within the cell. If this property is set to True, spreading will not apply to the selected flagged cells.
Include Flagged Readonly Cells in Totals
Set this to True to include locked base-level cell values when calculating spreading totals. True is the default.
Flag Selected Cells
Flags selected cells so the original amount in the cell is retained during the spreading process.
Clear Flags
Select this to clear any flagged cells.
File Operations
File Explorer
Use File Explorer to upload and download files.
Create Folder
This creates a new folder under the selected folder on the left-hand side of the File Explorer pane.
Delete Selected Folder/File
This deletes the selected folder on the left-hand side of the File Explorer pane or the selected file.
Edit Selected Folder/Edit Selected File Information
This edits the Description, Maintenance Group, and Access Group for the selected folder or file.
Upload File
This uploads the selected file and allows the user to save.
Download Selected File
This downloads the selected file and allows the user to save.
Download Selected File’s Content File
This downloads the selected file’s content file and allows the user to save.
Save to Server
Use this option to save your file to the OneStream folder structure. You can save files to any location matching your security access. If the file was opened from a OneStream location, it will be saved back to that location. If it wasn't, File Explorer will open.
Save Offline Copy
Use this to save an offline copy of the current worksheet without the functions. Users without the Excel Add-In can open this copy and see the saved values.
General
Object Lookup
Use the Object Lookup to insert objects from OneStream into Excel such as Foreign Exchange Rate Types when building formulas. If creating an Extensible Document in Excel, users can use the Object Lookup to insert Parameters, Substitution Variables, or Image Content. See Object Lookup in Presenting Data With Books, Cube Views and Dashboards for more details on this feature.
In-Sheet Actions
Create buttons to execute a Data Management Sequence, Submit data, or Refresh data, without leaving the sheet. See In-Sheet Actions.
Select Member
Select a Dimension Type from the drop-down list in order to view the Members of that Dimension. Select a Member of the hierarchy, and the Member name will display in the selected cell.
Preferences
Set Preferences for how to interact with and display your OneStream data. Settings include:
General
-
Enable Macros for OneStream Event Processing: If set to True, this enables Excel macros for OneStream API calls. The default is False.
-
Invalidate Old Data When Workbook is Opened: If set to True, this will force a data refresh on the opened workbook. The default is False.
-
Use Minimal Calculation for Refresh: This is for Excel Add-In only, not the Spreadsheet feature in OneStream Windows App. The default is True this will only calculate formulas and Excel functions in the active sheet. Set to False to revert to a full calculation of all workbooks and all sheets.
NOTE: Performance is best when Excel is set to use Manual Calculation Mode.
-
Disable Interactive User During Refresh: Accounts for a known Excel situation when running on certain touchscreen hardware. If the Refresh Sheet or Refresh Workbook is pressed but the cells containing functions do not complete their calculations when processed, change the Disable Interactive User During Refresh setting under Preferences to True.
NOTE: Setting this to True may result in incompatibility issues with other Excel Add-ins.
-
Retain All Formatting when Saving Offline: This is for Excel Add-In only, not Spreadsheet. The default is False to derive basic formatting and better performance. Set this to True to obtain all character by character formatting, this will force a data refresh on the opened workbook.
-
Use Add-In Compatibility Filter: When True, only cell selection change events, such as keystrokes or mouse clicks, are allowed. Third-party add-ins and macros cannot change cells. When False, users, third-party add-ins, and macros can make cell changes.
Quick View Double-Click Behavior
-
Default Expansion for Rows/Columns: This determines what level of expansion displays when a user double-clicks a Quick View Row or Column Header. NextLevel is the default setting and allows multiple expansion paths when a user double clicks a row or column header. There is also the ability to double-click an expanded item to collapse it again. This feature only works with the NextLevel setting.
Default Display Settings for New Quick Views and Default Suppression Settings for New Quick Views.
Display Context Pane
To display the OneStream task pane on the right-hand side of the screen, check this box. To hide the task pane, disable the box.
Tasks
Task Activity
View all running, scheduled, and completed tasks.
Automatic
The Excel Calculation icon has the option of Automatic, Automatic Except for Data Tables, and Manual. It is recommended that the Calculation be set to Manual when using OneStream] spreadsheets because the Automatic setting results in an Excel re-calculation every time a OneStream’s interactive workbook changes data (e.g., when navigating a Quick View). However, this is not forced because a user might prefer Excel’s Automatic calculation, especially when there is not a significant amount of OneStream data in the workbook.
